﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NCT.Framework;
using NCT.Framework.Database;
using System.Data.Common;
using System.Data;
using VISS.API.CustomUtils;

namespace VISS.API.Users {

    class UserSearchDA {

        private const string SP_QuickSearch = "dbo.UserSearch_QuickSearch";
        private const string SP_QuickSearchCount = "dbo.UserSearch_QuickSearchCount";
        private const string SP_GetMostFavoriteRandom = "dbo.UserSearch_GetMostFavoriteRandom";

        public static UserSrhResEnt CreateFromReader(IDataReader reader) {
            UserSrhResEnt item = new UserSrhResEnt();
            item.UserKey = ConvertUtils.ToString(reader["UserKey"]);
            item.Name = ConvertUtils.ToString(reader["Name"]);
            item.Gender = (EUserGender)ConvertUtils.ToByte(reader["Gender"]);
            item.BirthDate = ConvertUtils.ToDateTime(reader["BirthDate"]);
            item.CityID = ConvertUtils.ToShort(reader["CityID"]);
            item.City = ConvertUtils.ToString(reader["City"]);
            item.CountryID = ConvertUtils.ToShort(reader["CountryID"]);
            item.Country = ConvertUtils.ToString(reader["Country"]);
            item.EducationID = ConvertUtils.ToShort(reader["EducationID"]);
            item.Education = ConvertUtils.ToString(reader["Education"]);
            item.JobID = ConvertUtils.ToShort(reader["JobID"]);
            item.Job = ConvertUtils.ToString(reader["Job"]);
            item.ImageCount = ConvertUtils.ToInt(reader["ImageCount"]);
            item.Blast = ConvertUtils.ToString(reader["Blast"]);
            item.AvatarFileName = ConvertUtils.ToString(reader["AvatarFileName"]);
            item.AvatarImageStoreID = ConvertUtils.ToInt(reader["AvatarImageStoreID"]);
            item.AvatarStatus = (EUserAvatarStatus)ConvertUtils.ToByte(reader["AvatarStatus"]);
            return item;
        }

        public static UserSrhResEnt CreateFromReaderForNoelEvent(IDataReader reader) {
            EvtNoelUserSrhResEnt item = new EvtNoelUserSrhResEnt();
            item.UserKey = ConvertUtils.ToString(reader["UserKey"]);
            item.Name = ConvertUtils.ToString(reader["Name"]);
            item.Gender = (EUserGender)ConvertUtils.ToByte(reader["Gender"]);
            item.BirthDate = ConvertUtils.ToDateTime(reader["BirthDate"]);
            item.CityID = ConvertUtils.ToShort(reader["CityID"]);
            item.City = ConvertUtils.ToString(reader["City"]);
            item.CountryID = ConvertUtils.ToShort(reader["CountryID"]);
            item.Country = ConvertUtils.ToString(reader["Country"]);
            item.EducationID = ConvertUtils.ToShort(reader["EducationID"]);
            item.Education = ConvertUtils.ToString(reader["Education"]);
            item.JobID = ConvertUtils.ToShort(reader["JobID"]);
            item.Job = ConvertUtils.ToString(reader["Job"]);
            item.ImageCount = ConvertUtils.ToInt(reader["ImageCount"]);
            item.Blast = ConvertUtils.ToString(reader["Blast"]);
            item.AvatarFileName = ConvertUtils.ToString(reader["AvatarFileName"]);
            item.AvatarImageStoreID = ConvertUtils.ToInt(reader["AvatarImageStoreID"]);
            item.AvatarStatus = (EUserAvatarStatus)ConvertUtils.ToByte(reader["AvatarStatus"]);
            item.GoOut = ConvertUtils.ToBool(reader["GoOut"]);
            return item;
        }

        public static List<UserSrhResEnt> QuickSearch(UserSrhEnt srhEnt, EUserQuickSearchOrderType orderBy, long startRow, long endRow, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<UserSrhResEnt> result = new List<UserSrhResEnt>();
            string whereClause = BuildWhereClauseForQuickSearch(srhEnt);
            string orderClause = string.Empty;
            switch(orderBy) {
                case EUserQuickSearchOrderType.LastLogin:
                    orderClause = "ORDER BY (convert(varchar(30), u.LastLogin, 112) + convert(varchar(2), u.LastLogin, 108)) DESC, u.[ID];";
                    break;
                case EUserQuickSearchOrderType.Random:
                    orderClause = "ORDER BY NEWID();";
                    break;
                default:
                    orderClause = "ORDER BY (convert(varchar(30), u.LastLogin, 112) + convert(varchar(2), u.LastLogin, 108)) DESC, u.[ID];";
                    break;
            }
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_QuickSearch)) {
                db.AddParameter(cmd, "WhereClause", whereClause);
                db.AddParameter(cmd, "OrderClause", orderClause);
                db.AddParameter(cmd, "StartRow", startRow);
                db.AddParameter(cmd, "EndRow", endRow);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        UserSrhResEnt item = CreateFromReaderForNoelEvent(reader);
                        item.Age = Utils.CalculateAge(item.BirthDate);
                        result.Add(item);
                    }
                }
            }
            return result;
        }

        public static List<UserSrhResEnt> QuickSearchForNoelEvent(UserSrhEnt srhEnt, EUserQuickSearchOrderType orderBy, long startRow, long endRow, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<UserSrhResEnt> result = new List<UserSrhResEnt>();
            string whereClause = BuildWhereClauseForNoelEventQuickSearch(srhEnt);
            string orderClause = string.Empty;
            switch (orderBy) {
                case EUserQuickSearchOrderType.LastLogin:
                    orderClause = "ORDER BY (convert(varchar(30), u.LastLogin, 112) + convert(varchar(2), u.LastLogin, 108)) DESC, u.[ID];";
                    break;
                case EUserQuickSearchOrderType.Random:
                    orderClause = "ORDER BY NEWID();";
                    break;
                default:
                    orderClause = "ORDER BY (convert(varchar(30), u.LastLogin, 112) + convert(varchar(2), u.LastLogin, 108)) DESC, u.[ID];";
                    break;
            }
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_QuickSearch)) {
                db.AddParameter(cmd, "WhereClause", whereClause);
                db.AddParameter(cmd, "OrderClause", orderClause);
                db.AddParameter(cmd, "StartRow", startRow);
                db.AddParameter(cmd, "EndRow", endRow);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        UserSrhResEnt item = CreateFromReaderForNoelEvent(reader);
                        item.Age = Utils.CalculateAge(item.BirthDate);
                        result.Add(item);
                    }
                }
            }
            return result;
        }

        public static int QuickSearchCount(UserSrhEnt srhEnt, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = 0;
            string whereClause = BuildWhereClauseForQuickSearch(srhEnt);
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_QuickSearchCount)) {
                db.AddParameter(cmd, "WhereClause", whereClause);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToInt(reader["TotalRecord"]);
                    }
                }
            }
            return result;
        }

        public static int QuickSearchCountForNoelEvent(UserSrhEnt srhEnt, string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            int result = 0;
            string whereClause = BuildWhereClauseForNoelEventQuickSearch(srhEnt);
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_QuickSearchCount)) {
                db.AddParameter(cmd, "WhereClause", whereClause);
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    if (reader.Read()) {
                        result = ConvertUtils.ToInt(reader["TotalRecord"]);
                    }
                }
            }
            return result;
        }

        public static List<UserSrhResEnt> GetMostFavoriteRandom(EUserGender gender,string connectionName, DbConnection dbCon, DbTransaction dbTrans) {
            List<UserSrhResEnt> result = new List<UserSrhResEnt>();
            Database db = Database.CreateDatabase(connectionName);
            using (DbCommand cmd = db.CreateStoredCommand(SP_GetMostFavoriteRandom)) {
                db.AddParameter(cmd, "Gender", ConvertUtils.ToBool((byte)gender));
                using (IDataReader reader = db.ExecuteReader(cmd)) {
                    while (reader.Read()) {
                        result.Add(CreateFromReader(reader));
                    }
                }
            }
            return result;
        }

        #region Private Methods

        private static string BuildWhereClauseForQuickSearch(UserSrhEnt srhEnt) {
            string whereClause = "where ";
            if (srhEnt.CityID > 0) {
                whereClause += "UserGeneralProfile.CityID = " + srhEnt.CityID.ToString() + " And ";
            }
            if (srhEnt.CountryID > 0) {
                whereClause += "UserGeneralProfile.CountryID = " + srhEnt.CountryID.ToString() + " And ";
            }
            if (srhEnt.EducationID > 0) {
                whereClause += "UserGeneralProfile.EducationID = " + srhEnt.EducationID.ToString() + " And ";
            }
            if (srhEnt.JobID > 0) {
                whereClause += "UserGeneralProfile.JobID = " + srhEnt.JobID.ToString() + " And ";
            }
            if (srhEnt.Gender != EUserGender.Unknown) {
                whereClause += "UserGeneralProfile.Gender = " + ConvertUtils.ToByte(srhEnt.Gender).ToString() + " And ";
            }
            if (srhEnt.FromBirthDate > DateTimeUtils.MinValue) {
                whereClause += "UserGeneralProfile.BirthDate >= '" + srhEnt.FromBirthDate.ToString("MM/dd/yyyy") + "' And ";
            }
            if (srhEnt.ToBirthDate > DateTimeUtils.MinValue) {
                whereClause += "UserGeneralProfile.BirthDate <= '" + srhEnt.ToBirthDate.ToString("MM/dd/yyyy") + "' And ";
            }

            if (srhEnt.MostFavorite) {
            }
            whereClause += "1 = 1 ";
            whereClause += "And u.Status & " + (short)EUserStatus.Verified + " <> 0 ";

            //this is condition for not display System Administrator and is hidden user
            whereClause += "And u.Status & " + ((short)(EUserStatus.Admin | EUserStatus.Hidden)) + " = 0 ";

            return whereClause;
        }

        private static string BuildWhereClauseForNoelEventQuickSearch(UserSrhEnt srhEnt) {
            string whereClause = "where ";
            if (srhEnt.CityID > 0) {
                whereClause += "UserGeneralProfile.CityID = " + srhEnt.CityID.ToString() + " And ";
            }
            if (srhEnt.CountryID > 0) {
                whereClause += "UserGeneralProfile.CountryID = " + srhEnt.CountryID.ToString() + " And ";
            }
            if (srhEnt.EducationID > 0) {
                whereClause += "UserGeneralProfile.EducationID = " + srhEnt.EducationID.ToString() + " And ";
            }
            if (srhEnt.JobID > 0) {
                whereClause += "UserGeneralProfile.JobID = " + srhEnt.JobID.ToString() + " And ";
            }
            if (srhEnt.Gender != EUserGender.Unknown) {
                whereClause += "UserGeneralProfile.Gender = " + ConvertUtils.ToByte(srhEnt.Gender).ToString() + " And ";
            }
            if (srhEnt.FromBirthDate > DateTimeUtils.MinValue) {
                whereClause += "UserGeneralProfile.BirthDate >= '" + srhEnt.FromBirthDate.ToString("MM/dd/yyyy") + "' And ";
            }
            if (srhEnt.ToBirthDate > DateTimeUtils.MinValue) {
                whereClause += "UserGeneralProfile.BirthDate <= '" + srhEnt.ToBirthDate.ToString("MM/dd/yyyy") + "' And ";
            }
            if (((EvtNoelUserSrhEnt)srhEnt).GoOut) {
                whereClause += "ne.GoOut = 1 And ";
            }
            whereClause += "1 = 1 ";
            whereClause += "And u.Status & " + (short)EUserStatus.Verified + " <> 0 ";

            //this is condition for not display System Administrator and is hidden user
            whereClause += "And u.Status & " + ((short)(EUserStatus.Admin | EUserStatus.Hidden)) + " = 0 ";

            return whereClause;
        }

        #endregion
    }
}
